package TestSystemDB;

import javax.swing.*;

import java.awt.*;
import javax.swing.UIManager.*;
import javax.swing.border.*;

import TestSystemUtil.DBUtil;
import TestSystemUtil.DBconnect;

import net.proteanit.sql.DbUtils;

import java.awt.event.*;
import java.sql.*;

@SuppressWarnings("serial")
public class AdminFrame extends JFrame {
	public PreparedStatement pst = null;
	public Connection conn = null;
	public ResultSet rs = null;
	private JPanel choosePanel, searchPanel, formPanel, questionPanel;
	private JTextField answerTextField;
	private JComboBox tableComboBox, levelComboBox, fieldComboBox, classComboBox;
	private JTextField idTextField, pwdTextField, fullnameTextField, emailTextField, phoneTextField;
	private JLabel levelLabel, fieldLabel, contentLabel, choiceALabel, choiceBLabel, choiceCLabel, choiceDLabel, answerLabel;
	private JTextArea  contentTextArea, choiceATextArea, choiceBTextArea, choiceCTextArea, choiceDTextArea;
	private JToolBar controlToolbar;
	private JTable dbTable;
	private JScrollPane scrollPane_5;
	private CardLayout cl;
	String sql, table = null, field = null, form = null;
	int level;
	private JButton insertButton, deleteButton, updateButton;
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public AdminFrame() {
		conn = DBconnect.establishConnection();
		setResizable(false);
		setSize(new Dimension(800, 600));
		setLocationRelativeTo(null);
		getContentPane().setLayout(null);
		
		controlToolbar = new JToolBar();
		controlToolbar.setBounds(0, 0, 794, 31);
		getContentPane().add(controlToolbar);
		
		insertButton = new JButton("Insert");
		insertButton.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {
				if(!table.equals(" ")) {
					switch(table) {
					case "Users":
						if(idTextField.getText().isEmpty() || pwdTextField.getText().isEmpty() || fullnameTextField.getText().isEmpty() 
								|| classComboBox.getSelectedItem().toString().equals(" ")) {
							JOptionPane.showMessageDialog(null, "Important Fields are empty", "Register Fail", JOptionPane.ERROR_MESSAGE);
						} else {
							sql = "SELECT * FROM users WHERE username = ?";
							try {
								pst = conn.prepareStatement(sql);
								pst.setString(1, idTextField.getText());
								rs = pst.executeQuery();
								if(!rs.next()) {
									sql = "INSERT INTO users VALUES (default, ?, ?, ?, ?, ?, ?)";
									pst = conn.prepareStatement(sql);
									pst.setString(1, idTextField.getText());
									pst.setString(2, pwdTextField.getText());
									pst.setString(3, fullnameTextField.getText());
									pst.setString(4, classComboBox.getSelectedItem().toString());
									pst.setString(5, (emailTextField.getText().isEmpty()) ? "" : emailTextField.getText());
									pst.setString(6, (phoneTextField.getText().isEmpty()) ? "" : phoneTextField.getText());
									clearForm();
								} else {
								JOptionPane.showMessageDialog(null, "ID Exist", "Register Fail", JOptionPane.ERROR_MESSAGE);
								}
							} catch (SQLException ex) {
							}
						}
						break;
					case "Questions":
						if(levelComboBox.getSelectedItem().toString().equals(" ") || fieldComboBox.getSelectedItem().toString().equals(" ")
								|| contentTextArea.getText().isEmpty() || choiceATextArea.getText().isEmpty() || choiceDTextArea.getText().isEmpty()
								|| choiceBTextArea.getText().isEmpty() || choiceCTextArea.getText().isEmpty() ) {
							JOptionPane.showMessageDialog(null, "Important Fields are empty", "Insert Fail", JOptionPane.ERROR_MESSAGE);
						} else {
							String QID = createQID();
							System.out.println("QID: " + QID);
							sql = "INSERT INTO questions VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
							try {
								pst = conn.prepareStatement(sql);
								pst.setString(1, QID);
								pst.setInt(2, level);
								pst.setString(3, field);
								pst.setString(4, contentTextArea.getText());
								pst.setString(5, choiceATextArea.getText());
								pst.setString(6, choiceBTextArea.getText());
								pst.setString(7, choiceCTextArea.getText());
								pst.setString(8, choiceDTextArea.getText());
								pst.setString(9, answerTextField.getText());
								pst.execute();
								JOptionPane.showMessageDialog(null, "Insert new question successful", "Sucessful", JOptionPane.INFORMATION_MESSAGE);
							} catch (SQLException ex) {
								System.err.println("Insert Questions: " + ex);
							}
						}
						break;
					}
				}
			}
		});
		//insertButton.setIcon(new ImageIcon(AdminFrame.class.getResource("/resources/Knob Add.png")));
		controlToolbar.add(insertButton);
		
		deleteButton = new JButton("Delete");
		//deleteButton.setIcon(new ImageIcon(AdminFrame.class.getResource("/resources/Knob Remove.png")));
		controlToolbar.add(deleteButton);
		
		updateButton = new JButton("Update");
		//updateButton.setIcon(new ImageIcon(AdminFrame.class.getResource("/resources/edit-file.png")));
		controlToolbar.add(updateButton);
		
		choosePanel = new JPanel();
		choosePanel.setBorder(new TitledBorder(null, "Choose", TitledBorder.LEADING, TitledBorder.TOP, null, null));
		choosePanel.setBounds(0, 30, 115, 93);
		getContentPane().add(choosePanel);
		choosePanel.setLayout(null);
		
		tableComboBox = new JComboBox();
		tableComboBox.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent ev) {
				table = getChosenTable();
				form = getForm();
				System.out.println("Form: " + form);
				if(!table.equals(" ")) {
					sql = "SELECT * FROM " + table;
					   try {
				            pst = conn.prepareStatement(sql);
				            rs = pst.executeQuery();
				            dbTable.setModel(DbUtils.resultSetToTableModel(rs));
				            cl.show(formPanel, form);
				        } catch (Exception e) {
				            JOptionPane.showMessageDialog(null, e, "Choose Table Error", JOptionPane.ERROR_MESSAGE);
				        }
				}
			}
		});
		tableComboBox.setModel(new DefaultComboBoxModel(new String[] {" ", "Users", "Questions"}));
		tableComboBox.setBounds(6, 28, 103, 26);
		choosePanel.add(tableComboBox);
		
		searchPanel = new JPanel();
		searchPanel.setBorder(new TitledBorder(null, "Search", TitledBorder.LEADING, TitledBorder.TOP, null, null));
		searchPanel.setBounds(115, 33, 679, 90);
		getContentPane().add(searchPanel);
		
		formPanel = new JPanel();
		formPanel.setBorder(new TitledBorder(null, "Table Form", TitledBorder.LEADING, TitledBorder.TOP, null, null));
		formPanel.setBounds(0, 135, 226, 437);
		getContentPane().add(formPanel);
		cl = new CardLayout();
		formPanel.setLayout(cl);	
		
		
		questionPanel = new JPanel();
		formPanel.add(questionPanel, "questionCard");
		questionPanel.setLayout(null);
		
		levelLabel = new JLabel("Level :");
		levelLabel.setBounds(0, 6, 55, 16);
		questionPanel.add(levelLabel);
		
		fieldLabel = new JLabel("Field :");
		fieldLabel.setBounds(0, 34, 55, 16);
		questionPanel.add(fieldLabel);
		
		contentLabel = new JLabel("Content :");
		contentLabel.setBounds(0, 115, 55, 16);
		questionPanel.add(contentLabel);
		
		choiceALabel = new JLabel("Choice A :");
		choiceALabel.setBounds(0, 190, 71, 16);
		questionPanel.add(choiceALabel);
		
		choiceBLabel = new JLabel("Choice B :");
		choiceBLabel.setBounds(0, 242, 71, 16);
		questionPanel.add(choiceBLabel);
		
		choiceCLabel = new JLabel("Choice C :");
		choiceCLabel.setBounds(0, 295, 71, 16);
		questionPanel.add(choiceCLabel);
		
		choiceDLabel = new JLabel("Choice D :");
		choiceDLabel.setBounds(0, 344, 71, 16);
		questionPanel.add(choiceDLabel);
		
		answerLabel = new JLabel("Correct Answer :");
		answerLabel.setBounds(0, 383, 90, 16);
		questionPanel.add(answerLabel);
		
		levelComboBox = new JComboBox();
		levelComboBox.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				level = getChosenLevel();
			}
		});
		levelComboBox.setModel(new DefaultComboBoxModel(new String[] {" ", "Easy", "Medium", "Hard"}));
		levelComboBox.setBounds(67, 1, 125, 26);
		questionPanel.add(levelComboBox);
		
		fieldComboBox = new JComboBox();
		fieldComboBox.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				field = getChosenField();
				System.out.println("Field: "+ field);
			}
		});
		fieldComboBox.setModel(new DefaultComboBoxModel(new String[] {" ", "English", "Japanese", "Biology", "Geography"}));
		fieldComboBox.setBounds(67, 29, 125, 26);
		questionPanel.add(fieldComboBox);
		
		JScrollPane scrollPane_1 = new JScrollPane();
		scrollPane_1.setBounds(67, 174, 125, 45);
		questionPanel.add(scrollPane_1);
		
		choiceATextArea = new JTextArea();	
		scrollPane_1.setViewportView(choiceATextArea);
		choiceATextArea.setLineWrap(true);
		
		JScrollPane scrollPane_2 = new JScrollPane();
		scrollPane_2.setBounds(67, 225, 125, 48);
		questionPanel.add(scrollPane_2);
		
		choiceBTextArea = new JTextArea();
		scrollPane_2.setViewportView(choiceBTextArea);
		choiceBTextArea.setLineWrap(true);
		
		JScrollPane scrollPane_3 = new JScrollPane();
		scrollPane_3.setBounds(67, 276, 125, 48);
		questionPanel.add(scrollPane_3);
		
		choiceCTextArea = new JTextArea();
		choiceCTextArea.setLineWrap(true);
		scrollPane_3.setViewportView(choiceCTextArea);
		
		JScrollPane scrollPane_4 = new JScrollPane();
		scrollPane_4.setBounds(67, 327, 125, 44);
		questionPanel.add(scrollPane_4);
		
		choiceDTextArea = new JTextArea();
		choiceDTextArea.setLineWrap(true);
		scrollPane_4.setViewportView(choiceDTextArea);
		
		answerTextField = new JTextField();
		answerTextField.setBounds(102, 377, 90, 28);
		questionPanel.add(answerTextField);
		answerTextField.setColumns(10);
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(67, 67, 125, 95);
		questionPanel.add(scrollPane);
		
		contentTextArea = new JTextArea();
		contentTextArea.setLineWrap(true);
		scrollPane.setViewportView(contentTextArea);
		
		JPanel emptyPanel = new JPanel();
		formPanel.add(emptyPanel, "emptyCard");
		
		JPanel userPanel = new JPanel();
		formPanel.add(userPanel, "userCard");
		userPanel.setLayout(null);
		
		JLabel idLabel = new JLabel("Username :");
		idLabel.setBounds(6, 18, 65, 16);
		userPanel.add(idLabel);
		
		idTextField = new JTextField();
		idTextField.setBounds(83, 12, 109, 28);
		userPanel.add(idTextField);
		idTextField.setColumns(10);
		
		JLabel lblNewLabel_1 = new JLabel("Password :");
		lblNewLabel_1.setBounds(6, 63, 65, 16);
		userPanel.add(lblNewLabel_1);
		
		pwdTextField = new JTextField();
		pwdTextField.setBounds(83, 57, 109, 28);
		userPanel.add(pwdTextField);
		pwdTextField.setColumns(10);
		
		JLabel fullnameLabel = new JLabel("Fullname :");
		fullnameLabel.setBounds(6, 112, 65, 16);
		userPanel.add(fullnameLabel);
		
		fullnameTextField = new JTextField();
		fullnameTextField.setBounds(83, 106, 109, 28);
		userPanel.add(fullnameTextField);
		fullnameTextField.setColumns(10);
		
		JLabel classLabel = new JLabel("Class :");
		classLabel.setBounds(6, 159, 65, 16);
		userPanel.add(classLabel);
		
		JLabel emailLabel = new JLabel("Email :");
		emailLabel.setBounds(6, 210, 65, 16);
		userPanel.add(emailLabel);
		
		emailTextField = new JTextField();
		emailTextField.setBounds(83, 204, 109, 28);
		userPanel.add(emailTextField);
		emailTextField.setColumns(10);
		
		JLabel phoneLabel = new JLabel("Phone :");
		phoneLabel.setBounds(6, 256, 55, 16);
		userPanel.add(phoneLabel);
		
		phoneTextField = new JTextField();
		phoneTextField.setBounds(83, 250, 109, 28);
		userPanel.add(phoneTextField);
		phoneTextField.setColumns(10);
		
		classComboBox = new JComboBox();
		classComboBox.setModel(new DefaultComboBoxModel(new String[] {" ", "VNK58", "VNK57", "VNK56", "VNK55", "VNK54", "VNK53", "VNK52"}));
		classComboBox.setBounds(83, 154, 109, 26);
		userPanel.add(classComboBox);
		
		JPanel tablePanel = new JPanel();
		tablePanel.setBorder(new TitledBorder(null, "Table", TitledBorder.LEADING, TitledBorder.TOP, null, null));
		tablePanel.setBounds(238, 135, 550, 437);
		getContentPane().add(tablePanel);
		tablePanel.setLayout(null);
		
		scrollPane_5 = new JScrollPane();
		scrollPane_5.setBounds(12, 56, 532, 362);
		tablePanel.add(scrollPane_5);
		
		dbTable = new JTable();
		dbTable.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {
				int row = dbTable.getSelectedRow();
				String click = dbTable.getModel().getValueAt(row, 0).toString();
				switch(table) {
				case "Users":
					sql = "SELECT * FROM users WHERE \"UID\" = " + Integer.parseInt(click);
					try {
						pst = conn.prepareStatement(sql);
						rs = pst.executeQuery();
						rs.next();
						idTextField.setText(rs.getString(2));
						pwdTextField.setText(rs.getString(3));
						fullnameTextField.setText(rs.getString(4));
						int i = 0;
						while(!classComboBox.getSelectedItem().toString().equals(rs.getString(5))) {
							++i;
							classComboBox.setSelectedIndex(i);
						}
						emailTextField.setText((rs.getString(6).isEmpty()) ? "" : rs.getString(6));
						phoneTextField.setText((rs.getString(7).isEmpty()) ? "" : rs.getString(7));
					} catch (SQLException ex) {
						System.err.println(ex);
					}
					break;
				case "Questions":
					sql = "SELECT * FROM questions WHERE \"QID\" = '" + click +"'";
					try {
						pst = conn.prepareStatement(sql);
						rs = pst.executeQuery();
						rs.next();
						levelComboBox.setSelectedIndex(rs.getInt(2));
						setChosenField(rs.getString(3));
						contentTextArea.setText(rs.getString(4));
						choiceATextArea.setText(rs.getString(5));
						choiceBTextArea.setText(rs.getString(6));
						choiceCTextArea.setText(rs.getString(7));
						choiceDTextArea.setText(rs.getString(8));
						answerTextField.setText(rs.getString(9));
						
					} catch (SQLException ex) {
						
					}
					break;
				}
			}
		});
		scrollPane_5.setViewportView(dbTable);
		cl.show(formPanel, "emptyCard");
	}
	
	public int getChosenLevel() {
		int level = 0;
		switch (levelComboBox.getSelectedItem().toString()) {
		case "Hard":
			level =  3;
			break;
		case "Medium":
			level = 2;
			break;
		case "Easy":
			level = 1;
			break;
		}
		return level;
	}

	public String getChosenField() {
		switch(fieldComboBox.getSelectedItem().toString()) {
		case "English":
			return "ENG";
		case "Japanese":
			return "JP";
		case "Biology":
			return "BIO";
		case "Geography":
			return "GEO";
		default: 
			return null;
		}
	}
	
	public String getChosenTable() {
		return tableComboBox.getSelectedItem().toString();
	}
	
	public void setChosenField(String field) {
		switch(field) {
		case "ENG": 
			fieldComboBox.setSelectedIndex(1);
			break;
		case "JP": 
			fieldComboBox.setSelectedIndex(2);
			break;
		case "BIO": 
			fieldComboBox.setSelectedIndex(2);
			break;
		case "GEO": 
			fieldComboBox.setSelectedIndex(2);
			break;
		}
	}
	public String getForm() {
		String form = null;
		switch(tableComboBox.getSelectedItem().toString()) {
		case " ":
			form = "emptyCard";
			break;
		case "Users":
			form = "userCard";
			break;
		case "Questions":
			form = "questionCard";
			break;
		}
		return form;
	}
	
	public void clearForm() {
		idTextField.setText("");
		pwdTextField.setText("");
		fullnameTextField.setText("");
		classComboBox.setSelectedIndex(0);
		emailTextField.setText("");
		phoneTextField.setText("");
	}
	
	public String createQID() {
		String QID = null;
		level =  getChosenLevel();
		field = getChosenField();
		System.out.println(level + field);
		sql = "SELECT * FROM questions WHERE level = ? AND field = ? ORDER BY \"QID\" ASC";
		try {
			pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			pst.setInt(1, level);
			pst.setString(2, field);
			rs = pst.executeQuery();
			if(!rs.next()) {
				QID = String.format(level + field + "%04d", 1);
			} else {
				rs.beforeFirst();
				int prevID = 0, nextID;
				String nextQID;
				while(rs.next()) {
					nextQID = rs.getString(1);
					nextID = Integer.parseInt(nextQID.split("[A-Z]+")[1].trim());
					if(nextID != (prevID + 1))  QID = String.format(level + field + "%04d", prevID+1);
					else {
						prevID = nextID;
						if(rs.isLast()) QID = String.format(level + field + "%04d", nextID+1);
					}
				}
			}
		} catch (SQLException ex) {
			System.out.println("CreateQID: " + ex);
		}
		return QID;
	}
	@SuppressWarnings("rawtypes")
	public void tableComboBoxStateChanged(JComboBox comboBox) {
		System.out.println(comboBox.getSelectedItem());
	}
	public static void main (String[] args) {
		try {
			for(LookAndFeelInfo info : UIManager.getInstalledLookAndFeels()) {
				if("Nimbus".equals(info.getName())) {
					UIManager.setLookAndFeel(info.getClassName());
					break;
				}
			}
		} catch (Exception ex) {
			System.err.println("Set LookAndFeel: " + ex);
		}
		EventQueue.invokeLater(new Runnable() {
			@Override
			public void run() {
				new AdminFrame().setVisible(true);
			}
			
		});
	}
}
